package com.wwwc.index.ejb.user;

import java.sql.*;
import javax.sql.*;
import java.util.*;
import javax.ejb.*;
import javax.naming.*;
import java.rmi.RemoteException;

import com.wwwc.index.servlet.UserProfile;

public class UserBean implements SessionBean {
    private Connection con;

    public void ejbCreate() throws CreateException {}
    public void ejbRemove() {}
    public void ejbActivate() {}
    public void ejbPassivate() {}
    public void setSessionContext(SessionContext sc) {}

    public int tableExists(String table_name) {
	int found = 0;
        try {  
	    makeConnection();
	    String query = "SELECT TABLE_NAME FROM SYSTEM_TABLES WHERE TABLE_NAME='"+table_name+"';";
	    PreparedStatement prepStmt = con.prepareStatement(query);
	    ResultSet rs = prepStmt.executeQuery();
	    while (rs.next()) {
		if((rs.getString(1)).equals(table_name)) {
		    found = 1;
		}
	    }
	    rs.close();
	    prepStmt.close();
	    releaseConnection();
        } catch (Exception e) {
	    found = -1;
	    System.out.println("UserBean:tableExists:Error:"+e.getMessage());
        } 
	return found;
    } 

    public String createUserProfileTable() {
	StringBuffer sbf = new StringBuffer();
	sbf.append("CREATE TABLE USER_PROFILE_3WCENTER (");
	sbf.append("USER_NAME VARCHAR_IGNORECASE(20) PRIMARY KEY,");
	sbf.append("PASS_WORD VARCHAR(20),");
	sbf.append("ACCESS_LEVEL INTEGER,");
	sbf.append("EMAIL VARCHAR_IGNORECASE(100),");
	sbf.append("FIRST_NAME VARCHAR_IGNORECASE(100),");
	sbf.append("LAST_NAME VARCHAR_IGNORECASE(100),");
	sbf.append("PHOTO VARCHAR_IGNORECASE(500),");
	sbf.append("BIRTH_DAY TIMESTAMP,");
	sbf.append("GENDER VARCHAR_IGNORECASE(10),");
	sbf.append("EDUCATION VARCHAR_IGNORECASE(10),");
	sbf.append("MARITAL_STATUS VARCHAR_IGNORECASE(20),");
	sbf.append("INDUSTRY VARCHAR_IGNORECASE(20),");
	sbf.append("JOB_TITLE VARCHAR_IGNORECASE(20),");
	sbf.append("PHONE_AREA_CODE VARCHAR_IGNORECASE(20),");
	sbf.append("ZIP_CODE VARCHAR(20),");
	sbf.append("COUNTRY VARCHAR_IGNORECASE(200),");
	sbf.append("ACCOUNT_STATUS VARCHAR_IGNORECASE(10),");
	sbf.append("LAST_SIGN_IN BIGINT);");

        try {  
	    makeConnection();
	    PreparedStatement prepStmt = con.prepareStatement(sbf.toString());
	    ResultSet rs = prepStmt.executeQuery();
	    rs.close();
	    prepStmt.close();
	    releaseConnection();
        } catch (Exception e) {
	    return(e.getMessage());
        } 
	return "Excuting :"+sbf+"....\n Result:OK";
    } 

    public boolean usernamePasswordCheck(String user_name, String pass_word) {
	if(user_name == null || pass_word == null || user_name.length()==0 || pass_word.length()==0) {
	    System.out.println("UserBean:Error:101: username or password");
	    return false;
	}

	boolean ok = false;

	String query = "SELECT PASSWD FROM USERS WHERE USERNAME='"+user_name+"';";
	PreparedStatement prepStmt = null;
	ResultSet rs = null;
	ResultSetMetaData rsmd = null;

        try {
	    makeConnection();
	    prepStmt =  con.prepareStatement(query);
	    rs = prepStmt.executeQuery();
	    if(rs.next() && rs.getString(1).equals(pass_word)) {
		ok = true;
	    }

	    rs.close();
	    prepStmt.close();
	    releaseConnection();

        } catch (Exception e) {
	    System.out.println("UserBean:usernamePasswordCheck:Error:"+e);
	    releaseConnection();
        } 
        return ok;
    } 

    public boolean usernameExists(String user_name) {
	boolean exist = true;

	String query = "SELECT USERNAME FROM USERS WHERE USERNAME='"+user_name+"';";
	PreparedStatement prepStmt = null;
	ResultSet rs = null;

        try {
	    makeConnection();
	    prepStmt =  con.prepareStatement(query);
	    rs = prepStmt.executeQuery();
	    if(!rs.next()) {
	       exist = false;
	    }

	    rs.close();
	    prepStmt.close();
	    releaseConnection();

        } catch (Exception e) {
	    System.out.println("UserBean:usernamePasswordCheck:Error:"+e);
	    releaseConnection();
        } 
        return exist;
    } 

    public boolean emailHasBeenRegisted (String email) {
	boolean registed = false;
	String query = query = "SELECT EMAIL FROM USER_INFO WHERE EMAIL='"+email+"' AND STATUS='Y';"; 
	try {	
	    makeConnection();
	    PreparedStatement prepStmt =  con.prepareStatement(query);
	    ResultSet rs = prepStmt.executeQuery();
	    if(rs.next()) {
		registed = true;
	    }

	    rs.close();
	    prepStmt.close();
	    releaseConnection();
	}
	catch(SQLException se) {
	    releaseConnection();
	    System.out.println("UserBean:emailHasBeenRegisted:Error:"+se);
	}
        return registed;
    }

    public int addNewUser(String username, String password) {
	int error = -1;
	String query = "INSERT INTO USERS (USERNAME, PASSWD) VALUES (?, ?);";

	try {
	    makeConnection();
	    PreparedStatement prepStmt =  con.prepareStatement(query);
	    prepStmt.setString(1, username);
	    prepStmt.setString(2, password);
	    error = prepStmt.executeUpdate();
	    prepStmt.close();
	    releaseConnection();
	}
	catch (Exception e) {
	    System.out.println("UserBean:addNewUser:error:" + e);
        } 
	return error;
    }

    public int addNewUserProfile(String user, String pass, int access_level, String email, String fname, String lname, 
				 String photo, String birth_day, String gender, String edu, String marital_status, 
				 String industry, String job_title, String phone_area_code, String zip, String country, 
				 String activ_code) {
	int error = -1;
	if(addNewUser(user, pass) != 1) {
	    return error;
	}

	String query = "INSERT INTO USER_PROFILE_3WCENTER ("+
	    "USER_NAME, PASS_WORD, ACCESS_LEVEL, EMAIL,"+
	    "FIRST_NAME, LAST_NAME, PHOTO,"+
	    "BIRTH_DAY, GENDER, EDUCATION, MARITAL_STATUS, INDUSTRY, JOB_TITLE,"+
	    "PHONE_AREA_CODE, ZIP_CODE, COUNTRY, ACCOUNT_STATUS) VALUES ("+
	    "?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);";

	try {
	    makeConnection();
	    PreparedStatement prepStmt =  con.prepareStatement(query);
	    prepStmt.setString(1, user);
	    prepStmt.setString(2, pass);
	    prepStmt.setInt(3, access_level);
	    prepStmt.setString(4, email);
	    prepStmt.setString(5, fname);
	    prepStmt.setString(6, lname);
	    prepStmt.setString(7, photo);
	    prepStmt.setString(8, birth_day);
	    prepStmt.setString(9, gender);
	    prepStmt.setString(10, edu);
	    prepStmt.setString(11, marital_status);
	    prepStmt.setString(12, industry);
	    prepStmt.setString(13, job_title);
	    prepStmt.setString(14, phone_area_code);
	    prepStmt.setString(15, zip);
	    prepStmt.setString(16, country);
	    prepStmt.setString(17, activ_code);

	    error = prepStmt.executeUpdate();
	    prepStmt.close();
	    releaseConnection();
	}
	catch (Exception e) {
	    System.out.println("UserBean:addNewUserProfile:error:" + e);
        } 
       
	return error;
    }

    public int activeUserAccount(String user, String code) {
	int error = -1;
	String query ="UPDATE USER_PROFILE_3WCENTER SET ACCOUNT_STATUS=? WHERE USER_NAME=? AND ACCOUNT_STATUS=?;";

	try {
	    makeConnection();
	    PreparedStatement prepStmt =  con.prepareStatement(query);
	    prepStmt.setString(1, "Actived");
	    prepStmt.setString(2, user);
	    prepStmt.setString(3, code);
	    error = prepStmt.executeUpdate();
	    prepStmt.close();
	    releaseConnection();
	}
	catch (Exception e) {
	    System.out.println("UserBean:activeUserAccount:error:" + e);
        } 
	return error;
    }

    public String getUserPassword(String user_name){
	String password = null;
	String query = "SELECT PASSWD FROM USERS WHERE USERNAME='"+user_name+"';";
	try {	
	    makeConnection();
	    PreparedStatement prepStmt =  con.prepareStatement(query);
	    ResultSet rs = prepStmt.executeQuery();

	    if(rs.next()) {
		password = rs.getString(1);
	    }

	    rs.close();
	    prepStmt.close();
	    releaseConnection();
	}
	catch(SQLException e) {
	    releaseConnection();
	    System.out.println("UserBean:getUserPassword:Error:"+e);
	}
        return password;
    }

    public ArrayList getUserProfileByEmail(String email){
	ArrayList a = new ArrayList();
	UserProfile up = null;

	String query = "SELECT USER_NAME, PASS_WORD, ACCESS_LEVEL, FIRST_NAME, LAST_NAME, PHOTO, BIRTH_DAY, GENDER, EDUCATION,"+
	    "MARITAL_STATUS, INDUSTRY, JOB_TITLE, PHONE_AREA_CODE, ZIP_CODE, COUNTRY, ACCOUNT_STATUS, LAST_SIGN_IN "+
	    "FROM USER_PROFILE_3WCENTER WHERE EMAIL = ? ORDER BY LAST_SIGN_IN;";
	
	try {	
	    makeConnection();
	    PreparedStatement prepStmt =  con.prepareStatement(query);
	    prepStmt.setString(1, email);
	    ResultSet rs = prepStmt.executeQuery();

	    while(rs.next()) {
		up = new UserProfile(rs.getString(1),  //user_name 
				     rs.getString(2),  //password
				     rs.getInt(3),     //access_level 
				     email,            //email
				     rs.getString(4),  //first_name
				     rs.getString(5),  //last_name
				     rs.getString(6),  //photo
				     rs.getDate(7),    //birth_day 
				     rs.getString(8),  //gender 
				     rs.getString(9),  //edu 
				     rs.getString(10),  //marital_status
				     rs.getString(11), //industry 
				     rs.getString(12), //job_title
				     rs.getString(13), //phone_area_code
				     rs.getString(14), //zip_code
				     rs.getString(15), //country 
				     rs.getString(16), //account_status
				     rs.getString(17)); //last_sign_in
		a.add(up);
	    }

	    rs.close();
	    prepStmt.close();
	    releaseConnection();
	}
	catch(SQLException e) {
	    releaseConnection();
	    System.out.println("UserBean:getUserProfileByEmail:Error:"+e);
	}
	
        return a;
    }
    
    public Object getUserProfile(String user_name, String pass_word){
	UserProfile up = null;

	String query = "SELECT ACCESS_LEVEL, EMAIL, FIRST_NAME, LAST_NAME, PHOTO, BIRTH_DAY, GENDER, EDUCATION,"+
	    "MARITAL_STATUS, INDUSTRY, JOB_TITLE, PHONE_AREA_CODE, ZIP_CODE, COUNTRY, ACCOUNT_STATUS, LAST_SIGN_IN "+
	    "FROM USER_PROFILE_3WCENTER WHERE USER_NAME = ? AND PASS_WORD= ?;";
	
	try {	
	    makeConnection();
	    PreparedStatement prepStmt =  con.prepareStatement(query);
	    prepStmt.setString(1, user_name);
	    prepStmt.setString(2, pass_word);
	    ResultSet rs = prepStmt.executeQuery();

	    if(rs.next()) {
		up = new UserProfile(user_name, pass_word,
				     rs.getInt(1),     //access_level 
				     rs.getString(2),  //email
				     rs.getString(3),  //first_name
				     rs.getString(4),  //last_name
				     rs.getString(5),  //photo
				     rs.getDate(6),    //birth_day 
				     rs.getString(7),  //gender 
				     rs.getString(8),  //edu 
				     rs.getString(9),  //marital_status
				     rs.getString(10), //industry 
				     rs.getString(11), //job_title
				     rs.getString(12), //phone_area_code
				     rs.getString(13), //zip_code
				     rs.getString(14), //country 
				     rs.getString(15), //account_status
				     rs.getString(16)); //last_sign_in
	    }

	    rs.close();
	    prepStmt.close();
	    releaseConnection();
	}
	catch(SQLException e) {
	    releaseConnection();
	    System.out.println("UserBean:getUserProfile:Error:"+e);
	}
	
        return up;
    }

    public String update(String query) {
	String error = null;
	try {	
	    makeConnection();
	    PreparedStatement prepStmt =  con.prepareStatement(query);
	    prepStmt.executeUpdate();
	    prepStmt.close();
	    releaseConnection();
	}
	catch(SQLException e) {
	    releaseConnection();
	    error = e.getMessage();
	}
	catch(Exception e) {
	    releaseConnection();
	    error = e.getMessage();
	}
	return error;
    }

    ////////////////////////////////////////////////////////////////////
    // Database Routines
    ////////////////////////////////////////////////////////////////////

    private void makeConnection() {
        try {
	    InitialContext ic = new InitialContext();
            DataSource ds = (DataSource) ic.lookup("java:comp/env/jdbc/DefaultDS");
            con =  ds.getConnection();
        } catch (Exception e) {
	    releaseConnection();
	    System.out.println("EJB:UserBean: makeConnection error:" + e.getMessage());
        }
    }
  
    private void releaseConnection() {
        try {
            con.close();
        } catch (SQLException e) {
	    System.out.println("UserBean: Reaease connect error: " + e.getMessage());
        }
    }
}
